/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package net.hnilice.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.TreeSet;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.OldExcelFormatException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 *
 * @author swosh
 */
public class Parser {

    public Collection parseExcelFile(File excel) {
        InputStream myxls = null;
        HSSFWorkbook wb = null;
        try {
            myxls = new FileInputStream(excel);
            wb = new HSSFWorkbook(myxls);
        } catch (IOException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
        List<TerminExcel> listTerminExcel = new ArrayList();
        int numWb = wb.getNumberOfSheets();
        for (int s = 0; s < numWb; s++) {
            HSSFSheet sheet = wb.getSheetAt(s);
            int numR = 0;
            if (sheet != null) {
                numR = sheet.getPhysicalNumberOfRows();
            }
            for (int r = 0; r < numR; r++) {
                if (r > 0) { // Dont read first row....there is names of column
                    HSSFRow row = sheet.getRow(r);
                    TerminExcel terminExcel = new TerminExcel();
                    int numC = 0;
                    if (row != null) {
                        numC = row.getPhysicalNumberOfCells();
                    } else {
                        //listOk.add(Boolean.FALSE);
                    }
                    for (int c = 0; c < numC; c++) {
                        HSSFCell cell = row.getCell(c);
                        if (cell == null) {
                            c = numC + 1;
                        }
                        String nazUlice;
                        Date terCis;
                        String nazAkce;
                        int[] cisOrientacni;

                        switch (c) {
                            case 0:
                                if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                    nazUlice = cell.getStringCellValue().trim();
                                    terminExcel.setNazUlice(nazUlice);
                                } else {
                                    System.out.println("The Cell " + (c + 1) + " on the row " + (r + 1)
                                            + " on the sheet " + (s + 1) + " is not string format");
                                }
                                break;
                            case 1:
                                // TODO for date in string format...here is date string type but  date type format not string
                                if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                                    //String cellVal = cell.getStringCellValue();

                                    //int i;

                                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                        terCis = cell.getDateCellValue();
                                        terminExcel.setTerCis(terCis);
                                    } else {
                                        System.out.println("The Cell " + (c + 1) + " on the row " + (r + 1)
                                                + " on the sheet " + (s + 1) + " is not date format");
                                    }
                                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                    String terCisS = cell.getStringCellValue();
                                    SimpleDateFormat pattern = new SimpleDateFormat("dd.MM.yyyy");
                                    try {
                                        terCis = pattern.parse(terCisS);
                                        terminExcel.setTerCis(terCis);
                                    } catch (ParseException ex) {
                                        Logger.getLogger(Parser.class.getName()).log(Level.SEVERE, null, ex);
                                    }

                                } else {
                                    System.out.println("The Cell " + (c + 1) + " on the row " + (r + 1)
                                            + " on the sheet " + (s + 1) + " is not date format");
                                }
                                break;
                            case 2:
                                if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                    nazAkce = cell.getStringCellValue().trim();
                                    terminExcel.setNazAkce(nazAkce);
                                } else {
                                    System.out.println("The Cell " + (c + 1) + " on the row " + (r + 1)
                                            + " on the sheet " + (s + 1) + " is not string format");
                                }
                                break;

                            case 3:
                                if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                                    String cisOriCellS = cell.getStringCellValue().trim();
                                    cisOriCellS = cisOriCellS.replaceAll("[^\\,\\-\\d]", "");
                                    int difference = 0;
                                    int second;
                                    int first;
                                    Set<Integer> treeSetCisOri = new TreeSet<Integer>();
                                    //TODO make from String arrays of int cisOrientacni[]
                                    String[] cisOriSA = cisOriCellS.split(",");

                                    for (int i = 0; cisOriSA.length > i; i++) {
                                        String cisOriS = cisOriSA[i];
                                        if (cisOriS.contains("-")) {
                                            String[] cisOrisS2SA = cisOriS.split("-");
                                            // cisOrisSSA.length > 2 => not alowed expresion,
                                            // must be like 1-21, and not like 1-21-26
                                            if (cisOrisS2SA.length > 2) {
                                                System.out.println("The Cell " + (c + 1) + " on the row " + (r + 1)
                                                        + " on the sheet " + (s + 1) + " is not alowed expresion."
                                                        + "There is more -(dashes), than one");
                                            }
                                            first = Integer.parseInt(cisOrisS2SA[0]);
                                            second = Integer.parseInt(cisOrisS2SA[1]);
                                            difference = second - first;
                                            for (int j = 0; difference > j; j++) {
                                                // j + first e.g. 1 + 5 => 5 == first
                                                treeSetCisOri.add(Integer.valueOf(j + first));
                                            }
                                            treeSetCisOri.add(second);



                                        } else {
                                            treeSetCisOri.add(Integer.valueOf(cisOriS).intValue());
                                        }

                                    }
                                    int k = 0;
                                    cisOrientacni = new int[treeSetCisOri.size()];
                                    for (Iterator<Integer> it = treeSetCisOri.iterator(); it.hasNext();) {
                                        Integer integer = it.next();
                                        cisOrientacni[k++] = integer.intValue();

                                    }
                                    //empty before next loop
                                    treeSetCisOri.clear();
                                    terminExcel.setCisOrientacni(cisOrientacni);

                                } else {
                                    System.out.println("The Cell " + (c + 1) + " on the row " + (r + 1)
                                            + " on the sheet " + (s + 1) + " is not string format");
                                }
                                break;
                            default:
                                break;
                            //Ignore


                        }


                    }
                    listTerminExcel.add(terminExcel);
                }

            }

        }
        return listTerminExcel;
    }

    public Collection parseForSave(File excel) {
        List<TerminExcel> listTerminExcel = (List<TerminExcel>) parseExcelFile(excel);
        TreeSet<TerminCisOri> treeSetTerminCO = new TreeSet<TerminCisOri>();
        for (Iterator<TerminExcel> it = listTerminExcel.iterator(); it.hasNext();) {
            TerminExcel terminExcel = it.next();
            TerminCisOri terminCisOri = terminExcel.getTerminCisOri();

            if (terminCisOri.getCisOrientacni() != null && terminCisOri.getNazAkce() != null
                    && terminCisOri.getNazUlice() != null && terminCisOri != null) {
                treeSetTerminCO.add(terminCisOri);
            } //TODO termin excel error

        }
        return treeSetTerminCO;
    }

}
